﻿CREATE DATABASE [Yabe]

GO

USE [Yabe]
GO

/****** Object:  Table [dbo].[Posts]    Script Date: 09/20/2008 14:26:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Posts](
	[PostID] [int] IDENTITY(1,1) NOT NULL,
	[Body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Created] [datetime] NOT NULL CONSTRAINT [DF_Posts_Created]  DEFAULT (getdate()),
	[Modified] [datetime] NOT NULL CONSTRAINT [DF_Posts_Modified]  DEFAULT (getdate()),
	[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Title] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Categories] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Author] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[IsPublic] [bit] NOT NULL CONSTRAINT [DF_Posts_IsPublic]  DEFAULT ((0)),
	[Syndicated] [bit] NOT NULL CONSTRAINT [DF_Posts_Syndicated]  DEFAULT ((0)),
	[ShowOnFrontPage] [bit] NOT NULL CONSTRAINT [DF_Posts_ShowOnFrontPage]  DEFAULT ((0)),
	[AllowComments] [bit] NOT NULL CONSTRAINT [DF_Posts_AllowComments]  DEFAULT ((0)),
 CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED 
(
	[PostID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO 
 
/****** Object:  Table [dbo].[Links]    Script Date: 09/20/2008 14:25:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Links](
	[LinkID] [int] IDENTITY(1,1) NOT NULL,
	[LinkUrl] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LinkTitle] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LinkComment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LinkDate] [datetime] NOT NULL CONSTRAINT [DF_Links_LinkDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_Links] PRIMARY KEY CLUSTERED 
(
	[LinkID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

/****** Object:  Table [dbo].[BlogRoll]    Script Date: 09/20/2008 14:25:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BlogRoll](
	[BlogRollID] [int] IDENTITY(1,1) NOT NULL,
	[Url] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Blog] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Relationship] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_BlogRoll] PRIMARY KEY CLUSTERED 
(
	[BlogRollID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Comments]    Script Date: 09/20/2008 14:25:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Comments](
	[CommentID] [int] IDENTITY(1,1) NOT NULL,
	[PostID] [int] NOT NULL,
	[Author] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Comments_Author]  DEFAULT (N'Anonymous'),
	[Email] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Comments_Email]  DEFAULT (N' '),
	[Url] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Created] [datetime] NOT NULL CONSTRAINT [DF_Comments_Created]  DEFAULT (getdate()),
	[IsApproved] [bit] NOT NULL DEFAULT(1),
	[ClientIP] [nvarchar](32),
	[Modified] [datetime] NOT NULL CONSTRAINT [DF_Comments_Modified]  DEFAULT (getdate()),
 CONSTRAINT [PK_Comments] PRIMARY KEY CLUSTERED 
(
	[CommentID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[Comments]  WITH CHECK ADD  CONSTRAINT [FK_Comments_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[Posts] ([PostID])
GO
ALTER TABLE [dbo].[Comments] CHECK CONSTRAINT [FK_Comments_Posts]

GO
/****** Object:  Table [dbo].[Tags]    Script Date: 09/20/2008 14:26:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tags](
	[TagID] [int] IDENTITY(1,1) NOT NULL,
	[Tag] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_Tags] PRIMARY KEY CLUSTERED 
(
	[TagID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[PostTag]    Script Date: 09/20/2008 14:26:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PostTag](
	[PostTagID] [int] IDENTITY(1,1) NOT NULL,
	[PostID] [int] NOT NULL,
	[TagID] [int] NOT NULL,
 CONSTRAINT [PK_PostTag] PRIMARY KEY CLUSTERED 
(
	[PostTagID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_PostTag] UNIQUE NONCLUSTERED 
(
	[PostID] ASC,
	[TagID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PostTag]  WITH CHECK ADD  CONSTRAINT [FK_PostTag_Posts] FOREIGN KEY([PostID])
REFERENCES [dbo].[Posts] ([PostID])
GO
ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Posts]
GO
ALTER TABLE [dbo].[PostTag]  WITH CHECK ADD  CONSTRAINT [FK_PostTag_Tags] FOREIGN KEY([TagID])
REFERENCES [dbo].[Tags] ([TagID])
GO
ALTER TABLE [dbo].[PostTag] CHECK CONSTRAINT [FK_PostTag_Tags]

GO

/****** Object:  View [dbo].[vwTagCounts]    Script Date: 09/20/2008 14:29:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwTagCounts]
AS
SELECT ISNULL(dbo.Tags.Tag, 'Uncategorized') AS Tag, COUNT(dbo.Posts.PostID) AS TagCount
FROM      dbo.Posts LEFT OUTER JOIN
                      dbo.PostTag ON dbo.PostTag.PostID = dbo.Posts.PostID LEFT OUTER JOIN
                      dbo.Tags ON dbo.PostTag.TagID = dbo.Tags.TagID
GROUP BY dbo.PostTag.TagID, dbo.Tags.Tag

GO

